<?php
require_once("inc.member.php");

$this_title="$vars[member_title] - ".__("Export Genealogy");
$page_title=$content_title=__("Export Genealogy");

$td_width = 180;

require_once("inc.genealogy_tab.php");

if($_GET['__req']){
	$user = $r_user;
	if(!in_array($get_s['network'], array('s','p'))){
		$errmsg .= __("Please select a network.")."<br />";
	}
	
	if(!$errmsg){
		if($get_s['network'] == 's'){
			$which_network = 'ref';
			$export_network = "Sponsor Hierarchy";
			$file_network = "sponsor";
		}elseif($get_s['network'] == 'p'){
			$which_network = 'matrix_upline';
			$export_network = "Placement Hierarchy";
			$file_network = "placement";
		}
		
		$date=$get_s['date'];
		$year=substr($date, 0, 4);
		$month=substr($date, 4, 2);
		$calc_date['start'] = date('Y-m-d H:i:s', mktime(0,0,0,$month,1,$year));
		$calc_date['end'] = date('Y-m-d H:i:s', mktime(23,59,59,$month+1,0,$year));
		$export_date = date('M Y', strtotime($calc_date['start']));
		$uplines = $user['id'];
		$level = 1;
		do{
			$t = @mysql_num_rows($r=mysql_query($s="select * from $db->users where $which_network in ($uplines) and status!='terminated'"));
			unset($uplines);
			for($i=0;$i<$t;$i++){
				$down = mysql_fetch_assoc($r);
				if(!isset($di[$down[$which_network]])){
					$di[$down[$which_network]] = 0;
				}
				$downlines[$level][$down[$which_network]]['downline'][$di[$down[$which_network]]]['id'] = $down['id'];
				$downlines[$level][$down[$which_network]]['downline'][$di[$down[$which_network]]]['code'] = $down['code'];
				$downlines[$level][$down[$which_network]]['downline'][$di[$down[$which_network]]]['name'] = $down['name'];
				$downlines[$level][$down[$which_network]]['downline'][$di[$down[$which_network]]]['ref'] = $down['ref'];
				$downlines[$level][$down[$which_network]]['downline'][$di[$down[$which_network]]]['matrix_upline'] = $down['matrix_upline'];
				$di[$down[$which_network]]++;
				$uplines .= $down['id'].',';
			}
			$uplines = rtrim($uplines, ',');
			$level++;
		}while($uplines);
		for($i=count($downlines);$i>0;$i--){
			$level = $i;
			foreach($downlines[$i] as $upline_id => $details){
				$ddetails = $details['downline'];
				for($j=0,$t2=count($ddetails);$j<$t2;$j++){
					$downline_id = $ddetails[$j]['id'];
					$ps_a = $ps_b = $pgs_a = $pgs_b = 0;
					if(isset($downlines[($level+1)][$downline_id])){
						$pgs_a += $downlines[($level+1)][$downline_id]['pgs_a'];
						$pgs_b += $downlines[($level+1)][$downline_id]['pgs_b'];
					}
					$ps_a = @mysql_result(mysql_query("select sum(total_bv) from $db->purchase where uid='$downline_id' and status='confirmed' and member_sale='y' and first_sale='y' and no_bv!='y' and cdate>='$calc_date[start]' and cdate<='$calc_date[end]'"), 0);
					$ps_b = calc_personal_sales($downline_id, $calc_date['start'], $calc_date['end']);
					$pgs_a += $ps_a;
					$pgs_b += $ps_b;
					$downlines[$level][$upline_id]['downline'][$j]['ps_a'] = $ps_a;
					$downlines[$level][$upline_id]['downline'][$j]['ps_b'] = $ps_b;
					$downlines[$level][$upline_id]['downline'][$j]['pgs_a'] = $pgs_a;
					$downlines[$level][$upline_id]['downline'][$j]['pgs_b'] = $pgs_b;
					$downlines[$level][$upline_id]['pgs_a'] += $pgs_a;
					$downlines[$level][$upline_id]['pgs_b'] += $pgs_b;
				}
			}
		}
		for($i=1,$t=count($downlines);$i<=$t;$i++){
			$level = $i;
			$run_no = 1;
			foreach($downlines[$i] as $upline_id => $details){
				$ddetails = $details['downline'];
				for($j=0,$t2=count($ddetails);$j<$t2;$j++){
					$downline_id = $ddetails[$j]['id'];
					$downline_name = $ddetails[$j]['name'];
					$ps_a = $ddetails[$j]['ps_a'];
					$ps_b = $ddetails[$j]['ps_b'];
					$pgs_a = $ddetails[$j]['pgs_a'];
					$pgs_b = $ddetails[$j]['pgs_b'];
					$downline_ref = $ddetails[$j]['ref'];
					$downline_upline = $ddetails[$j]['matrix_upline'];
					$sponsor = mysql_fetch_assoc(mysql_query("select name from $db->users where id='$downline_ref'"));
					$upline = mysql_fetch_assoc(mysql_query("select name from $db->users where id='$downline_upline'"));
					$row[$level] .= 
					"<tr>".
						"<td>$run_no</td>".
						"<td>$downline_id</td>".
						"<td>$downline_name</td>".
						"<td>".number_format($ps_a, 2)."</td>".
						"<td>".number_format($pgs_a, 2)."</td>".
						"<td>".number_format($ps_b, 2)."</td>".
						"<td>".number_format($pgs_b, 2)."</td>".
						"<td>".($downline_ref? "$downline_ref: $sponsor[name]" : "N/A")."</td>".
						"<td>".($downline_upline? "$downline_upline: $upline[name]" : "N/A")."</td>".
					"</tr>\n";
					$run_no++;
				}
			}
		}
		
		//level 0 pgs
		$pgs_a = $pgs_b = 0;
		if($downlines[1]){
			foreach($downlines[1] as $upline_id => $details){
				$pgs_a += $downlines[1][$upline_id]['pgs_a'];
				$pgs_b += $downlines[1][$upline_id]['pgs_b'];
			}
		}
		$ps_a = @mysql_result(mysql_query("select sum(total_bv) from $db->purchase where uid='$user[id]' and status='confirmed' and member_sale='y' and first_sale='y' and no_bv!='y' and cdate>='$calc_date[start]' and cdate<='$calc_date[end]'"), 0);
		$ps_b = calc_personal_sales($user['id'], $calc_date['start'], $calc_date['end']);
		$pgs_a += $ps_a;
		$pgs_b += $ps_b;
		$row[0] = 
		"<tr>".
			"<td>1</td>".
			"<td>$user[code]</td>".
			"<td>$user[name]</td>".
			"<td>".number_format($ps_a, 2)."</td>".
			"<td>".number_format($pgs_a, 2)."</td>".
			"<td>".number_format($ps_b, 2)."</td>".
			"<td>".number_format($pgs_b, 2)."</td>".
			"<td></td>".
			"<td></td>".
		"</tr>\n";
		
		//EXCEL export
		ob_start();
		?>
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
	<meta http-equiv="Content-type" content="text/html;charset=utf-8" />
<style id="Classeur1_16681_Styles">
</style>
<style type='text/css'>
td{
	border:1px solid #000;
}
tr.e_header td{
	font-weight:bold;
	background:#aaa;
}
</style>
</head>
<body>
<div id="Classeur1_16681" align=center x:publishsource="Excel">
<table x:str border=0 cellpadding=0 cellspacing=0 style="border-collapse: collapse">
<tr class=e_header><td nowrap colspan=9>User ID: <?php echo $user['code']; ?>, Date Range: <?php echo $export_date; ?>, Network: <?php echo $export_network; ?><br />Exported at <?php echo ndate($vars['date_format']); ?></td></tr>
<tr class=e_header><td nowrap>No</td><td nowrap>User ID</td><td nowrap>Name</td><td nowrap>PS-A (BV)</td><td nowrap>PGS-A (BV)</td><td nowrap>PS-B (BV)</td><td nowrap>PGS-B (BV)</td><td nowrap>Sponsor</td><td nowrap>Upline</td></tr>
<?php
for($i=0,$t=count($row);$i<$t;$i++){
	echo "<tr class=e_header><td nowrap colspan=9>Level $i</td></tr>\n";
	echo $row[$i];
	echo "<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>\n";
}
?>
</table>
</div>
</body>
</html>
		<?php 

		$xls = ob_get_clean();
		$filename = "genealogy_{$file_network}_$get_s[date]_".ndate('Ymd').".xls";
		header ( "Content-Type: application/force-download" );
		header ( "Content-Type: application/octet-stream" );
		header ( "Content-Type: application/download" );
		header ( "Content-Type: application/ms-excel" );
		header ( "Content-Disposition: attachment; filename=".$filename );
		header ( "Content-Transfer-Encoding: binary" );
		header ( "Accept-Ranges: bytes" );
		header ( "Content-Length: ".strlen ( $xls ) );
		echo $xls;
		exit;
	}
	
	$errmsg=$errmsg? format_err("There is some error(s), please correct them before continuing:<br />\n<br />\n$errmsg") : "";
}

$form_fields=array("uid"=>"","network"=>"s");
foreach($form_fields as $field => $default){
	$dis[$field]=$get_s[$field]? $get_s[$field] : $default;
}

$start_time = strtotime($r_user['cdate']);
$year_start = date('Y', $start_time);
$month_start = date('n', $start_time);
$year_end=ndate('Y');
$month_end=ndate('n');
$ii=0;
if($year_end > $year_start){
	for($j=$month_start;$j<=12;$j++){
		$yearmonth=strval($year_start).strval($j);
		$r_comm_date[$ii++]=$yearmonth;
		$r_comm_date_d[$yearmonth]=date('M-Y', mktime(0,0,0,$j,1,$year_start));
	}
	for($i=$year_start+1;$i<=$year_end;$i++){
		for($j=1;$j<=$month_end;$j++){
			$yearmonth=strval($i).strval($j);
			$r_comm_date[$ii++]=$yearmonth;
			$r_comm_date_d[$yearmonth]=date('M-Y', mktime(0,0,0,$j,1,$i));
		}
	}
}else{
	for($i=$year_start;$i<=$year_end;$i++){
		for($j=$month_start;$j<=$month_end;$j++){
			$yearmonth=strval($i).strval($j);
			$r_comm_date[$ii++]=$yearmonth;
			$r_comm_date_d[$yearmonth]=date('M-Y', mktime(0,0,0,$j,1,$i));
		}
	}
}
$date_select=build_select($r_comm_date, $r_comm_date_d, $get_s['date']? $get_s['date'] : ndate('Y').ndate('n'), 'date', $inputbox_style);
$tab_content=($errmsg? "
$errmsg" : "")."
<form name='user_form' method='get' action='$this_file'>
<input type='hidden' name='__req' value='1' />
<p>".__("To export genealogy, click 'Export Genealogy'.")."</p>
<table class='pbt_table'>
	<tr>
		<td>".__('Network').":</td>
		<td><input type='radio' name='network' value='s'".($dis['network'] == 's'? " checked='checked'" : '')." id='opt_ns' /> <label for='opt_ns'>".__('Sponsor Hierarchy')."</label>
			<input type='radio' name='network' value='p'".($dis['network'] == 'p'? " checked='checked'" : '')." id='opt_np' /> <label for='opt_np'>".__('Placement Hierarchy')."</label>
		</td>
	</tr>
	<tr>
		<td>".__('Date').":</td>
		<td>$date_select</td>
	</tr>
	<tr>
		<td colspan='2' class='center'><input type='submit' value=\"".__("Export Genealogy")."\" /></td>
	</tr>
</table>
</form>";
$tab_content = str_replace("<%tab_content%>", $tab_content, $tab);
$content="<h2>$page_title</h2>$tab_content";

print format_member_page($content, $this_title, $content_title, $css . $jvscript);
?>